<%@page language="java" pageEncoding="UTF-8"%>
<%@page import="java.util.*"%>
<%@page import="com.mysql.jdbc.Driver"%>
<%@page import="java.sql.*"%>
<%@page import="com.mysql.jdbc.ResultSetMetaData"%>

<%
    String path = request.getContextPath();
    String basePath = request.getScheme() + "://"
            + request.getServerName() + ":" + request.getServerPort()
            + path + "/";

    /** 链接数据库参数 **/
    String driverName = "com.mysql.jdbc.Driver"; //驱动名称
    String DBUser = "root"; //mysql用户名
    String DBPasswd = ""; //mysql密码
    String DBName = "webshop"; //数据库名

    //数据库完整链接地址
    String connUrl = "jdbc:mysql://localhost/" + DBName + "?user="
            + DBUser + "&password=" + DBPasswd;

    //加载数据库驱动
    Class.forName(driverName).newInstance();

    //链接数据库并保存到 conn 变量中
    Connection conn = DriverManager.getConnection(connUrl);

    //申明
    Statement stmt = conn.createStatement();

    //设置字符集
/*     stmt.executeQuery("SET NAMES UTF8"); */

    //要执行的 sql 查询
    String sql = "SELECT goods_id, goods_name, goods_price FROM mall_goods ";

    //取得结果
    ResultSet rs = stmt.executeQuery(sql);
%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
    <head>
        <base href="<%=basePath%>">

        <title>Jsp链接mysql数据库测试</title>

        <meta http-equiv="pragma" content="no-cache">
        <meta http-equiv="cache-control" content="no-cache">
        <meta http-equiv="expires" content="0">
        <meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
        <meta http-equiv="description" content="This is my page">
        <!--
    <link rel="stylesheet" type="text/css" href="styles.css">
    -->

    </head>

    <body>
  
        <ul style="width: 65%">
            <li style="font-weight: bold;">
                <span style="width: 10%;">商品ID</span>
                <span style="width: 40%;">商品名称</span>
                <span style="width: 10%;">商品价格</span>
            </li>
            <%
                /** 打印结果 **/
                
                while (rs.next()) {
            %>
            <li>
                <span style="width: 10%;"><%=rs.getString

("goods_id")%></span>
                <span style="width: 40%;"><%=rs.getString

("goods_name")%></span>
                <span style="width: 10%;">￥<%=rs.getString("goods_price")%>

元</span>
            </li>
            <%
                }
            %>
        </ul>
        <form name="form1" method="post" action="query.jsp">
  <p> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;查询书籍 </p>
  <p><label>输入商品编号</label> 
    <label for="textfield"></label>
    <input type="text" name="textfield" id="textfield">
  </p>
  <p>
    <input type="submit" name="button" id="button" value="提交">
  </p>
</form>
<form name="form2" method="post" action="add.jsp">
  <p> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;添加书籍 </p>
  <p><label>商品编号</label> 
    <label for="textfield"></label>
    <input type="text" name="textfield" id="textfield">
  　<label>商品名</label>
  <label for="textfield2"></label>
  <input type="text" name="textfield2" id="textfield2">
  　</p>
  <p><label>商品价格</label>&nbsp;
    <label for="textfield3"></label>
    <input type="text" name="textfield3" id="textfield3">
  </p>
  <p>
    <input type="submit" name="button" id="button" value="添加">
  </p>
</form>
        <form name="form3" method="post" action="delete.jsp">
  <p> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;删除书籍 </p>
  <p><label>输入商品编号</label> 
    <label for="textfield"></label>
    <input type="text" name="textfield" id="textfield">
  </p>
  <p>
    <input type="submit" name="button" id="button" value="删除">
  </p>
</form>
<form name="form4" method="post" action="alt.jsp">
  <p> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;修改数据 </p>
  <p><label>商品编号</label> 
    <label for="textfield"></label>
    <input type="text" name="textfield" id="textfield">
  　<label>商品名</label>
  <label for="textfield2"></label>
  <input type="text" name="textfield2" id="textfield2">
  　</p>
  <p><label>商品价格</label>&nbsp;
    <label for="textfield3"></label>
    <input type="text" name="textfield3" id="textfield3">
  </p>
  <p>
    <input type="submit" name="button" id="button" value="修改">
  </p>
</form>
    </body>
</html>
<%
    /** 关闭连接 **/
    conn.close();
    stmt.close();
    rs.close();
%>
